Final Dataset

dataset retail

dataset retail
    table stocks transactions daily_ranked_sales
    table tape_transactions code_counts
    table scissor_transactions tapes_and_scissors
    table tapes_or_scissors filtered_stocks
    reftable daily_sales
    join stocks inner join transactions
       on stocks.Code = transactions.stock_code

table stocks

table stocks
    data_source retail ds
    sql `online_retail`.`stocks`
    column is_tape
        sql description like '%TAPE%'
        type bool 
    column code
        sql code
        type string
    column description
        sql description
        type string
    column number_of_rows
        sql Count(*)
        type int

table transactions

table transactions
    data_source retail ds
    sql `online_retail`.`transactions`
    column tape_cost
        sql """case when stocks.is_tape 
            then invoice_qnty*unit_price else 0 end"""
        type float
        default_aggregate none
    column tx_id
        sql tx_id
        type int
        default_aggregate none
    column invoice_num
        sql invoice_num
        type string
    column stock_code
        sql stock_code
        type string
    column invoice_qnty
        sql invoice_qnty
        type int
    column _invoice_date
        sql invoice_date
        type string
        hidden true
    column invoice_date
        sql cast(_invoice_date as DATE)
        type datetime
    column unit_price
        sql unit_price
        type float
        format $0.00
    column customer_id
        sql customer_id
        type int
        default_aggregate none
    column country
        sql country
        type string
    column number_of_rows
        sql Count(*)
        type int
    column amount
        sql sum(invoice_qnty*unit_price)
        type float
    column transaction_amount
        sql sum(invoice_qnty*unit_price)
        fixed invoice_num
        type float
    column avg_transaction_amount
        sql avg(transaction_amount)
        type float
        format $0.00
    column transaction_amount2
        sql sum(invoice_qnty*unit_price)
        include invoice_num
        type float
    column avg_transaction_amount2
        sql avg(transaction_amount2)
        type float
        format $0.00

table tape_transactions

table tape_transactions
    sql """SELECT t.invoice_num AS invoice_num, 
            t.stock_code AS stock_code,
            t.unit_price AS unit_price, t.country AS country
        FROM `online_retail`.`stocks` AS s
        JOIN `online_retail`.`transactions` AS t 
        ON s.code = t.stock_code
        WHERE s.description like '%TAPE%'"""
    column invoice_num
        sql invoice_num
        type string
    column stock_code
        sql stock_code
        type string
    column unit_price
        sql unit_price
        type int
        default_aggregate none

table code_counts

table code_counts
    sql {stocks}
    column code
        sql code
        type string
    column number_of_rows
        sql number_of_rows
        type int
        default_aggregate none

table scissor_transactions

table scissor_transactions
    sql """SELECT t.stock_code AS stock_code,
            t.unit_price/2 AS unit_price, t.country AS country
        FROM `online_retail`.`stocks` AS s
        JOIN `online_retail`.`transactions` AS t 
        ON s.code = t.stock_code
        WHERE s.description like '%SCISSOR%'"""
    column invoice_num
        sql ("unknown")
        type string
    column stock_code
        sql stock_code
        type string
    column unit_price
        sql unit_price
        type int
        default_aggregate none

table tapes_and_scissors

table tapes_and_scissors
    sql {tape_transactions} UNION ALL {scissor_transactions}
    column stock_code
        sql stock_code
        type string
    column unit_price
        sql unit_price
        type float
        format $0.00
        default_aggregate none

table daily_sales

table daily_sales
    sql {transactions invoice_num invoice_date amount alias=transactions}
    column invoice_num
        sql invoice_num
        type string
    column invoice_date
        sql invoice_date
        type datetime
    column _amount
        sql amount
        type float
        hidden true
    column sale_amount
        sql sum(_amount)
        type float
        format $0.00
    column sale_rank
        sql RANK() OVER (ORDER BY sale_amount desc)
        type int

table daily_ranked_sales

table daily_ranked_sales
    sql {daily_sales invoice_num invoice_date sale_amount sale_rank}
    column invoice_num
        sql invoice_num
        type string
    column invoice_date
        sql invoice_date
        type datetime
    column sale_amount
        sql sale_amount
        type float
        format $0.00
        default_aggregate none
    column sale_rank
        sql sale_rank
        type int
        default_aggregate none

table tapes_or_scissors

table tapes_or_scissors
    parameter kind
        type string
        default_value TAPE
        allowed_value TAPE
        allowed_value SCISSOR
    sql """$bipp_if( $bipp_equal("$kind", "TAPE"), 
        "{tape_transactions}", "{scissor_transactions}" )"""
    column stock_code
        sql stock_code
        type string
    column unit_price
        sql unit_price
        type int
        default_aggregate none

table filtered_stocks

table filtered_stocks
    data_source retail ds
    sql `online_retail`.`stocks`
    parameter containing
        type string
        default_value ""
    column description
        sql description
        type string
    column number_of_rows
        sql Count(*)
        type int
    column contains
        sql """case when description LIKE '%$containing%' then 'yes' 
            else 'no' end"""
        type string